﻿/*
 * 
 *	simple mysql connection test:
 *	connects to a local mysql server and runs multi-line queries taken from the console and prints outputs; terminates with an empty query
 *	minimal error handling - mysql syntax errors are caught an printed, but all other errors will cause unhandled exceptions
 *
 */

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace csDBTest
{
    class dbTest
    {
		public static void Main()
		{
			//MySqlConnection conn = new MySqlConnection("Server=localhost;Username=repoClient;Password=This is a repo password.");
			MySqlConnection conn = new MySqlConnection("Server=96.32.28.47;Username=repoClient;Password=This is a repo password.;Database=researchrepo");
			MySqlCommand cmd = new MySqlCommand("", conn);

			string q;
			while(true)
			{
				// init empty query
				q = "";
				// read multiple lines, terminated with semi-colon (or just end on an empty string)
				do
				{
					Console.Write(q == "" ? ">> " : " > ");	// ">>" denotes start of new query, ">" denotes continuing query
					q += (q == "" ? "" : " ") + Console.ReadLine();
				}
				while(q.Length > 0 && q[q.Length-1] != ';');
				if (q == "")
				{
					break;
				}
				try
				{
					conn.Open();
				}
				catch (Exception e)
				{
					unrollException(e);
					continue;
				}
				cmd.CommandText = q;

				// repackage as list-of-lists and track max length of each field for pretty-printing
				ArrayList resTable = new ArrayList();
				int numFields = 0;
				int[] fieldSizes = null;
				string temp;

				try
				{
					MySqlDataReader data = cmd.ExecuteReader();
					numFields = data.FieldCount;
					fieldSizes = new int[numFields];
					
					resTable.Add(new string[data.FieldCount]);
					// print column names
					for (int i = 0; i < numFields; i++)
					{
						temp = data.GetName(i).ToString();
						((string[]) resTable[resTable.Count-1])[i] = temp;
						fieldSizes[i] = temp.Length;
					}
					// print rows
					while(data.Read())
					{
						resTable.Add(new string[numFields]);
						for (int i = 0; i < numFields; i++)
						{
							temp = data.GetValue(i).ToString();
							((string[]) resTable[resTable.Count-1])[i] = temp;
							// update
							if (temp.Length > fieldSizes[i])
							{
								fieldSizes[i] = temp.Length;
							}
						}
					}
					data.Close();
				}
				catch(MySqlException e)
				{
					unrollException(e);
				}
				finally
				{
					conn.Close();
				}

				// pretty print
				Console.WriteLine();
				// top border
				for (int j = 0; j < numFields; j++)
				{
					Console.Write("+-" + new string('-', fieldSizes[j]) + "-");
				}
				Console.WriteLine("+");
				// table content
				for (int i = 0; i < resTable.Count; i++)
				{
					// divider between column names and content
					if (i == 1)
					{
						for (int j = 0; j < numFields; j++)
						{
							Console.Write("+-" + new string('-', fieldSizes[j]) + "-");
						}
						Console.WriteLine("+");
					}
					// column names (j=0) and content (j>0)
					for (int j = 0; j < numFields; j++)
					{
						Console.Write("| " + ((string []) resTable[i])[j] + new string(' ', fieldSizes[j]-((string []) resTable[i])[j].Length) + " ");
					}
					Console.WriteLine("|");
				}
				// bottom border
				for (int j = 0; j < numFields; j++)
				{
					Console.Write("+-" + new string('-', fieldSizes[j]) + "-");
				}
				Console.WriteLine("+\n");
			}
		}

		/***
			print full exception (ie, with each InnerException)
		*/
		static void unrollException(Exception e)
		{
			Console.WriteLine("\tException(s):");
			for (int i = 0; e != null; e = e.InnerException, i++)
			{
				Console.WriteLine("\t" + i + ": " + e.Message);
			}
		}
    }
}